const express = require("express");
const router = express.Router();
const db = require("../../utils/db");
router.post("/overviewTableList", (req, res) => {
  let { time, page, pageSize } = req.body;
  let select = `SELECT reg_id,reg_s_id,s_name,s_sex,s_age,s_tel,reg_type,reg_user_id,u_name,reg_time FROM (SELECT * FROM register_record JOIN patient ON register_record.reg_s_id=patient.s_id) AS newtable JOIN doctor ON newtable.reg_user_id=doctor.u_id WHERE reg_time='${time}'`;
  let sel = `SELECT reg_id,reg_s_id,s_name,s_sex,s_age,s_tel,reg_type,reg_user_id,u_name,reg_time FROM (SELECT * FROM register_record JOIN patient ON register_record.reg_s_id=patient.s_id) AS newtable JOIN doctor ON newtable.reg_user_id=doctor.u_id WHERE reg_time='${time}' LIMIT ${
    (page - 1) * pageSize
  },${pageSize}`;
  db.query(select, (err, resu) => {
    if (err) {
    } else {
      db.query(sel, (error, result) => {
        if (error) {
          res.send({ err: error, msg: "error" });
        } else {
          res.send({ data: result, msg: "success", total: resu.length });
        }
      });
    }
  });
});
router.post("/weekDay", (req, res) => {
  let { day } = req.body;
  let sql = `SELECT SUM(a_price) AS total,m_price * a_count AS total2, a_time FROM expense_record JOIN medicinal_information 
  ON expense_record.a_m_id = medicinal_information.m_id WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= DATE(a_time) GROUP BY a_time`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});
// 收入趋势
router.post("/monthDay", (req, res) => {
  let { day } = req.body;
  let sql = `SELECT SUM(a_price) AS total,m_price * a_count AS total2, a_time FROM expense_record JOIN medicinal_information 
  ON expense_record.a_m_id = medicinal_information.m_id WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(a_time) GROUP BY a_time`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      res.send({ data: data, msg: "success" });
    }
  });
});

// 统计
router.post("/totalData", (req, res) => {
  let { day } = req.body;
  let sql = `SELECT COUNT(reg_id) AS total,reg_time FROM register_record 
  WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= DATE(reg_time) GROUP BY reg_time`;
  let sql2 = ` SELECT COUNT(reg_id) AS total1 ,reg_time FROM register_record 
  WHERE reg_status=1 AND DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= DATE(reg_time) GROUP BY reg_time`;
  let sql3 = `SELECT SUM(a_price) AS total,m_price * a_count AS total2, a_time 
  FROM expense_record JOIN medicinal_information 
  ON expense_record.a_m_id = medicinal_information.m_id 
  WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= DATE(a_time) GROUP BY a_time`;
  db.query(sql, (err, data) => {
    if (err) {
      res.send({ err, msg: "error" });
    } else {
      db.query(sql2, (err2, data2) => {
        if (err2) {
          res.send({ err2: err, msg: "error" });
        } else {
          db.query(sql3, (err3, data3) => {
            if (err3) {
              res.send({ err3: err, msg: "error" });
            } else {
              res.send({
                data1: data,
                data2: data2,
                data3: data3,
                msg: "success",
              });
            }
          });
        }
      });
    }
  });
});
module.exports = router;
